package br.com.mworks.DAO;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.mworks.DAO.impl.CategoriaImpl;
import br.ufc.mworks.model.Categoria;

public class CategoriaDao extends Conexao implements CategoriaImpl {
	public void criaTabelas() throws ClassNotFoundException {

		try {
			open();

			String sqlString = "CREATE TABLE Categoria ("
					+ "[CategoriaID] [int] IDENTITY(1,1) NOT NULL,"
					+ "[Nome] [nvarchar](50) NOT NULL)";

			// Use the connection to create the SQL statement.
			stmt = conn.createStatement();

			// Execute the statement.
			stmt.executeUpdate(sqlString);

			close();
			// Provide a message when processing is complete.
			System.out.println("Processing complete.");

		} catch (Exception e) {
			System.out.println("Exception " + e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				// Close resources.
				if (null != conn)
					conn.close();
				if (null != stmt)
					stmt.close();
				if (null != rs)
					rs.close();
			} catch (SQLException sqlException) {
				// No additional action if close() statements fail.
			}
		}
	}

	public void criaIndices() {

		try {
			open();
			// Define the SQL string.
			String sqlString = "CREATE CLUSTERED INDEX index1 "
					+ "ON Categoria (CategoriaID)";

			// Use the connection to create the SQL statement.
			stmt = conn.createStatement();

			// Execute the statement.
			stmt.executeUpdate(sqlString);

			close();
			// Provide a message when processing is complete.
			System.out.println("Processing complete.");

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void save(Categoria categoria) {
		try {
			open();
			int temp = 0;
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT MAX(id) FROM aluno;");
			while (rs.next()) {
				temp = rs.getInt(1) + 1;
			}
			String sqlString = "SET IDENTITY_INSERT Categoria ON "
					+ "INSERT INTO Categoria " + "(CategoriaID,Nome) "
					+ "VALUES(" + temp + ", '" + categoria.getNome() + "')";

			stmt.executeUpdate(sqlString);
			close();
		} catch (SQLException e) {
			close();
			e.printStackTrace();
		}

	}

	public List<Categoria> list() {

		try {
			open();
			List<Categoria> categorias = new ArrayList<Categoria>();
			stmt = conn.createStatement();

			String sqlString = "SELECT CategoriaID, Nome FROM Categoria";

			rs = stmt.executeQuery(sqlString);
			while (rs.next()) {
				Categoria categoria = new Categoria(rs.getInt("CategoriaID"),
						rs.getString("Nome"));
				categorias.add(categoria);
			}
			close();
			return categorias;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}
}
